#include "sql.h"

SqlHelper& SqlHelper::getInstance()
{
    static SqlHelper inst;
    return inst;
}

SqlHelper::SqlHelper()
{
    deleteDataBase();
    createConnection();
    createTables();
}

SqlHelper::~SqlHelper()
{

}

void SqlHelper::createConnection()
{
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("face_identify.db");
    if (!db.open()) {
        qDebug() << "Cannot open database:" << db.lastError();
    } else {
        qDebug() << "Create database succ";
    }
}


void SqlHelper::createTables()
{
    QString createRecordTable = R"(
        CREATE TABLE IF NOT EXISTS record (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            time TEXT NOT NULL
        )
    )";

    QSqlQuery query(db);
    if (!query.exec(createRecordTable)) {
        qDebug() << "Failed to create record table:" << query.lastError();
    }
}

void SqlHelper::deleteDataBase()
{
    if (db.isValid()) {
        db.close();
        QSqlDatabase::removeDatabase("face_identify.db");
    }
    if (QFile::exists("face_identify.db")) {
            if (QFile::remove("face_identify.db")) {
                qDebug() << "Database file deleted successfully.";
            } else {
                qDebug() << "Failed to delete database file.";
            }
        } else {
            qDebug() << "Database file does not exist.";
    }
}

void SqlHelper::insertRecord(const QString& name)
{
    QString time = QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");

    QSqlQuery query(db);
    query.prepare("INSERT INTO record (name, time) VALUES (:name, :time)");
    query.bindValue(":name", name);
    query.bindValue(":time", time);

    qDebug() << "name" << name << "time" << time;

    if (!query.exec()) {
        qDebug() << "insert record Error" << query.lastError().text();
        qDebug() << "With executed query:" << query.lastQuery();
    }
}

QList<QPair<QString, QString>> SqlHelper::getRecords() {
    QList<QPair<QString, QString>> records;

    if (!db.isOpen() && !db.open()) {
        qDebug() << "Failed to open the database:" << db.lastError();
        return records;
    }

    QSqlQuery query(db);
    if (!query.exec("SELECT name, time FROM record")) {
        qDebug() << "Query execution error:" << query.lastError();
        return records;
    }

    while (query.next()) {
        QString name = query.value(0).toString();
        QString time = query.value(1).toString();
        records.append(qMakePair(name, time));
    }

    return records;
}
